package com.topcheer.yaml2Properties;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;

/**
 * @author xutao
 * @Package com.topcheer.yaml2Properties
 * @Description: ${todo}
 * @date 2020/4/15 20:40
 */
public class POITest {

    public static void main(String[] args) throws IOException {
//        generateXLS();
        handleRowCellComment("D:\\writerPostil.xls","我的工作表","插件批注成功!插件批注成功!","新的批注");
    }

    private static void generateXLS() throws IOException {
        //创建工作簿对象
        HSSFWorkbook wb=new HSSFWorkbook();
        //创建工作表对象
        HSSFSheet sheet=wb.createSheet("我的工作表");
        //创建绘图对象
        HSSFPatriarch p=sheet.createDrawingPatriarch();
        //创建单元格对象,批注插入到4行,1列,B5单元格
        HSSFCell cell=sheet.createRow(4).createCell(1);
        //插入单元格内容
        cell.setCellValue(new HSSFRichTextString("批注"));
        //获取批注对象
        //(int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2)
        //前四个参数是坐标点,后四个参数是编辑和显示批注时的大小.
        HSSFComment comment=p.createComment(new HSSFClientAnchor(0,0,0,0,(short)3,3,(short)5,6));
        //输入批注信息
        comment.setString(new HSSFRichTextString("插件批注成功!插件批注成功!"));
        //添加作者,选中B5单元格,看状态栏
        comment.setAuthor("toad");
        //将批注添加到单元格对象中
        cell.setCellComment(comment);
        //创建输出流
        FileOutputStream out=new FileOutputStream("D:\\writerPostil.xls");
        wb.write(out);
        wb.close();
        //关闭流对象
        out.close();
    }

    /**
     * excel文件处理批注
     * @param filePath  文件路径
     * @param sheetName 处理的sheet页名称
     * @param oldContent 需要替换的批注内容
     * @param newContent 替换的批注内容
     */
    public static void handleRowCellComment(String filePath, String sheetName, String oldContent, String newContent) {
        File file = new File(filePath);
        //获取文件名  判断文件是.xls 或 .xlsx
        String fileName = file.getName();

        int excelType = 0;
        FileInputStream inputStream = null;
        Workbook wb = null;
        Iterator<Sheet> sheetIterator = null;
        FileOutputStream out = null;
        try {
            inputStream = new FileInputStream(file);
            if (fileName.endsWith("xls")) {
                wb = new HSSFWorkbook(inputStream);
                excelType = 1;
            }
            //所有的sheet页
            sheetIterator = wb.sheetIterator();
            while (sheetIterator.hasNext()) {
                Sheet sheet = sheetIterator.next();
                if (sheet.getSheetName().endsWith(sheetName)) {
                    //获取表头信息
                    Row header = sheet.getRow(4);
                    if (header != null) {
                        Cell cell = header.getCell(1);
                        String stringComment;
                        //有批注
                        if (cell != null && cell.getCellComment() != null) {
                            //获取批注
                            stringComment = cell.getCellComment().getString().toString();
                            if (stringComment.contains(oldContent)) {
                                stringComment = stringComment.replace(oldContent, newContent);
                            }
                            //写入批注
                            cell.getCellComment().setString(new HSSFRichTextString(stringComment));
                        }
                    }
                }
            }
            out = new FileOutputStream(file);
            wb.write(out);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (out != null) {
                try {
                    out.flush();
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
